/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import DTO.Products;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.XMLConstants;
import javax.xml.transform.sax.SAXSource;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.validation.Validator;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

/**
 *
 * @author TuPA
 */
public class ProductModel {

    public static boolean updateProductType(Products products, String id, int positionProduct) {
        DBUtil util = new DBUtil();
        Connection conn = null;
        Statement stm = null;
        String query = null;

        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            query = "Update Product set "
                    + "proType='" + products.getProduct().get(positionProduct).getProType() + "', "
                    + "noSell='" + products.getProduct().get(positionProduct).getNoSell() + "' "
                    + "where proID='" + id + "'";
            stm = conn.createStatement();
            stm.executeUpdate(query);
            stm.close();
            conn.close();
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    public static void updateProductDetail(Products products, String id, int positionProduct) {
        DBUtil util = new DBUtil();
        Connection conn = null;
        Statement stm = null;
        String query1 = null;
        String query2 = null;
        String query3 = null;
        ResultSet result1 = null;
        ResultSet result2 = null;

        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            query1 = "Select catalogID From Catalog "
                    + "Where catalogName='" + products.getProduct().get(positionProduct).getCatalogName() + "'";
            stm = conn.createStatement();
            result1 = stm.executeQuery(query1);
            query2 = "Select brandID From Brand "
                    + "Where brandName='" + products.getProduct().get(positionProduct).getBrandName() + "'";
            stm = conn.createStatement();
            result2 = stm.executeQuery(query2);
            while (result1.next() & result2.next()) {
                query3 = "Update Product set "
                        + "proName='" + products.getProduct().get(positionProduct).getProName() + "', "
                        + "Price='" + products.getProduct().get(positionProduct).getPrice() + "', "
                        + "Description='" + products.getProduct().get(positionProduct).getDescription() + "', "
                        + "catalogID='" + result1.getString("catalogID") + "', "
                        + "brandID='" + result2.getString("brandID") + "', "
                        + "Image='" + products.getProduct().get(positionProduct).getImage() + "', "
                        + "proNumber='" + products.getProduct().get(positionProduct).getProNumber() + "', "
                        + "proFrom='" + products.getProduct().get(positionProduct).getProFrom() + "' "
                        + "where proID='" + id + "'";
                stm = conn.createStatement();
                stm.executeUpdate(query3);
            }
            stm.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static boolean Insert(String proName, int Price, String Description, String Image,
            int catalogID, int proType, int proNumber, String proFrom, int brandID, int noSell) {
        DBUtil util = new DBUtil();
        Connection conn = null;
        String sql = null;
        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            sql = "Insert into Product(proName, Price, Description, Image,"
                    + " catalogID, proType, proNumber, proFrom, brandID, noSell) "
                    + "Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement stm = conn.prepareStatement(sql);
            stm.setString(1, proName);
            stm.setInt(2, Price);
            stm.setString(3, Description);
            stm.setString(4, Image);
            stm.setInt(5, catalogID);
            stm.setInt(6, proType);
            stm.setInt(7, proNumber);
            stm.setString(8, proFrom);
            stm.setInt(9, brandID);
            stm.setInt(10, noSell);
            int result = stm.executeUpdate();
            stm.close();
            conn.close();
            if (result > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean XMLvalidateSchema(String xmlFilePatch, String xsdFilePatch) {
        try {
            SchemaFactory sf = SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
            File schemaFile = new File(xsdFilePatch);
            Schema schema = sf.newSchema(schemaFile);
            Validator validator = schema.newValidator();
            InputSource inputFile = new InputSource(new BufferedReader(new FileReader(xmlFilePatch)));
            validator.validate(new SAXSource(inputFile));
        } catch (SAXException ex) {
            Logger.getLogger(OrderModel.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
}
